package com.fescoservice.service;

import java.util.List;


import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.fescoservice.dao.ApplicationDAO;
import com.fescoservice.dao.CompanyDAO;
import com.fescoservice.dao.CustomerDAO;
import com.fescoservice.exception.FSException;
import com.fescoservice.model.Application;
import com.fescoservice.model.Company;
import com.fescoservice.model.Customer;
import com.fescoservice.util.MiscUtil;
import com.fescoservice.util.Constants;
@Service
public class CustomerService {
	private static final Logger log = LogManager.getLogger(CustomerService.class);
	@Autowired
	private CompanyDAO companyDAO;

	@Autowired 
	private CustomerDAO customerDAO;
	
	@Autowired 
	private ApplicationDAO applicationDAO;
	

	public void updateCustomer(final Customer customer) throws FSException{
		 StringBuffer hql = new StringBuffer(" update Customer set  ");
			hql.append("custFirstnameEn = :custFirstnameEn,"); 	
			hql.append("custLastnameEn = :custLastnameEn,"); 
			hql.append("passId = :passId,");
			hql.append("passValidDate = :passValidDate,"); 	
			hql.append("custNameCn = :custNameCn,"); 	
			hql.append("birthDate = :birthDate,"); 	
			hql.append("phone = :phone,"); 	
			hql.append("companyName = :companyName,"); 	
			if(!MiscUtil.isNullOrEmpty(customer.getCompanyId())){
				hql.append("companyId = :companyId,"); 	
			}
			hql.append("companyAddr = :companyAddr,"); 	
			hql.append("custAddr = :custAddr where custId = :custId");
			customerDAO.update(hql.toString(),customer);
	}	
	
	public void deleteCustomer(Customer customer) throws FSException{		
		String hql = " delete from Customer c where c.custId = :custId";
		System.out.println("customer.getCustId():"+customer.getCustId());

		if(applicationDAO.findByProperty("custId", customer.getCustId()).size()>0){
			throw new FSException("FS_ERROR_DELETE_CUSTOMER","");
		}
		customerDAO.delete(hql,customer);					
	}	
	
	
	/**
	 * count(1)
	 * @param language
	 * @return
	 * @throws FSException
	 */
	public Long countCustomers(final String serchOptionSel,final String serchOptionValue)  throws FSException{
		final StringBuffer sql = new StringBuffer("SELECT COUNT(*) AS CNT FROM T_CUSTOMER ");
		if(!MiscUtil.isNullOrEmpty(serchOptionSel)&&!MiscUtil.isNullOrEmpty(serchOptionValue)){
			if(Constants.CUSTOMER_PASS_SEL.equals(serchOptionSel)){
				sql.append(" where PASS_ID = '"+serchOptionValue+"'");
			}
			if(Constants.CUSTOMER_NAME_EN_SEL.equals(serchOptionSel)){
				sql.append(" where concat(cust_firstname_en,' ',cust_lastname_en) like  '%"+serchOptionValue+"%'");
			}
			if(Constants.CUSTOMER_COMPANY_SEL.equals(serchOptionSel)){
				sql.append(" where COMPANY_NAME like  '%"+serchOptionValue+"%'");
			}
			
		}
		return customerDAO.countCustomers(sql.toString(),serchOptionSel,serchOptionValue);
	}
	
	public Long countCustomers4HR(final String serchOptionSel,final String serchOptionValue,final int companyId)  throws FSException{
		final StringBuffer sql = new StringBuffer("SELECT COUNT(*) AS CNT FROM T_CUSTOMER  WHERE COMPANY_ID ="+companyId);
		if(!MiscUtil.isNullOrEmpty(serchOptionSel)&&!MiscUtil.isNullOrEmpty(serchOptionValue)){
			if(Constants.CUSTOMER_PASS_SEL.equals(serchOptionSel)){
				sql.append(" and PASS_ID = '"+serchOptionValue+"'");
			}
			if(Constants.CUSTOMER_NAME_EN_SEL.equals(serchOptionSel)){
				sql.append(" and concat(cust_firstname_en,' ',cust_lastname_en) like  '%"+serchOptionValue+"%'");
			}
			System.out.println(sql.toString());
		}
		return customerDAO.countCustomers(sql.toString(),serchOptionSel,serchOptionValue);
	}
	
	public List getCustomers4HR(final String serchOptionSel,final String serchOptionValue,final int companyId,final int first) throws FSException{
		
		StringBuffer sql = new StringBuffer("from Customer c where c.companyId = :companyId");
		
		if(!MiscUtil.isNullOrEmpty(serchOptionSel)&&!MiscUtil.isNullOrEmpty(serchOptionValue)){
			if(Constants.CUSTOMER_PASS_SEL.equals(serchOptionSel)){
				sql.append(" and passId = :passId ");
			}
			if(Constants.CUSTOMER_NAME_EN_SEL.equals(serchOptionSel)){
				sql.append(" and concat(custFirstnameEn,' ',custLastnameEn)  like '%'|| :custFirstnameEn ||'%'");
			}
		}
		List list = customerDAO.findCustomerByProperty(sql.toString(),serchOptionSel,serchOptionValue,companyId,first);

		return list;
	}
	
	/**
	 * list
	 * @param language
	 * @return
	 * @throws FSException 
	 */
	public List getAllCustomers(final String serchOptionSel,final String serchOptionValue,final int first) throws FSException{
		
		StringBuffer sql = new StringBuffer("from Customer c  ");
		
		if(!MiscUtil.isNullOrEmpty(serchOptionSel)&&!MiscUtil.isNullOrEmpty(serchOptionValue)){
			if(Constants.CUSTOMER_PASS_SEL.equals(serchOptionSel)){
				sql.append(" where passId = :passId ");
			}
			if(Constants.CUSTOMER_NAME_EN_SEL.equals(serchOptionSel)){
				sql.append(" where concat(custFirstnameEn,' ',custLastnameEn)  like '%'|| :custFirstnameEn ||'%'");
			}
			if(Constants.CUSTOMER_COMPANY_SEL.equals(serchOptionSel)){
				sql.append(" where companyName  like '%'|| :companyName ||'%'");
			}
		}
		List list = customerDAO.findCustomerByProperty(sql.toString(),serchOptionSel,serchOptionValue,0,first);

		return list;
	}
	
	
	/**
	 * 新增
	 * @param transientInstance
	 * @throws FSException 
	 */
	public void saveCustomer(Customer transientInstance) throws FSException{
		//String sql = "SELECT SEQ_T_CUSTOMER.nextval as custId FROM dual ";
		//String custId = String.valueOf((long)customerDAO.getCustomerId(sql));
		//transientInstance.setCustId(custId);
		customerDAO.save(transientInstance);
	}	
	
	
	/**
	 * 新增
	 * @param transientInstance
	 * @throws FSException 
	 */
	public void saveApplication(Application transientInstance) throws FSException{
		applicationDAO.save(transientInstance);
	}	
	
	
	


	/**
	 * count(1)
	 * @param language
	 * @return
	 * @throws FSException
	 */
	public Long countApplications(final String userId,final String userRole,final String userDept,final String custId)  throws FSException{
		final StringBuffer sql = new StringBuffer("SELECT COUNT(DISTINCT A.APPLY_ID) AS CNT FROM T_APPLICATION A  WHERE 1 = 1 ");
		
		if(Constants.OPERATOR_ROLE_CEO.equals(userRole)){
			sql.append("");		
		}else if(Constants.OPERATOR_ROLE_MANAGER.equals(userRole)){			
			sql.append(" and A.CREATOR_DEPT = "+userDept);			
		}else if(Constants.OPERATOR_ROLE_BUSSINESS_MAN.equals(userRole)){
			sql.append(" and A.CREATOR_ID = "+userId);	
		}
		if(!MiscUtil.isNullOrEmpty(custId)){
			sql.append(" AND A.CUST_ID = "+custId);
		}
		
		return customerDAO.countApplications(sql.toString(),userId,userRole,userDept,custId);
	}
	
	
	
	
	
	/**
	 * list
	 * @param language
	 * @return
	 * @throws FSException 
	 */
	public List getApplications(final String userId,final String userRole,final String userDept,final String custId) throws FSException{
		
		StringBuffer sql = new StringBuffer("from Application c where 1=1 ");
		
		if(Constants.OPERATOR_ROLE_CEO.equals(userRole)){
			// do nothing
		}else if(Constants.OPERATOR_ROLE_MANAGER.equals(userRole)){			
			sql.append(" and c.creatorDept = :deptId");			
		}else if(Constants.OPERATOR_ROLE_BUSSINESS_MAN.equals(userRole)){
			sql.append(" and c.creatorId = :creatorId");	
		}
		if(!MiscUtil.isNullOrEmpty(custId)){
			sql.append(" and c.custId = :custId");	
		}
		
		List list = customerDAO.getApplications(sql.toString(),userId,userRole,userDept,custId);

		return list;
	}
	
	
	
	/**
	 * list
	 * @param language
	 * @return
	 * @throws FSException 
	 */
	public List viewApplications(final String applyId,final String custId) throws FSException{
		
		StringBuffer sql = new StringBuffer("from Application c where 1=1 ");
		if(!MiscUtil.isNullOrEmpty(custId)){
			sql.append(" and c.custId = :custId");	
		}
		if(!MiscUtil.isNullOrEmpty(applyId)){
			sql.append(" and c.applyId = :applyId");	
		}
		
		List list = customerDAO.viewApplications(sql.toString(),applyId,custId);

		return list;
	}
	
	
	
	
	
	public Company findById(String id) {
		List list = companyDAO.findByProperty("compId",id);
		Company comp = (Company)list.get(0);
		 return comp;
	}
	
	public Customer findCustomerById(final String custId ) throws FSException{
		Customer  cust= customerDAO.findById(Integer.parseInt(custId));
		return cust;
	}
	
	
	
	/**
	 * list
	 * @param language
	 * @return
	 * @throws FSException 
	 */
	public List custLogin(final String userId,final String userPass) throws FSException{		
		
		StringBuffer sql = new StringBuffer("");
		if(userId.length()<5){
			sql = new StringBuffer("from Customer c where c.custFirstnameEn = :userId and c.birthDate = :userPass ");
		}else{
			sql = new StringBuffer("from Customer c where c.custFirstnameEn like '%'|| :userId ||'%'  and c.birthDate = :userPass ");
		}
		
		List list = customerDAO.custLogin(sql.toString(),userId,userPass);
		return list;
	}
	
	/**
	 * list
	 * @param language
	 * @return
	 * @throws Exception 
	 */
	public List queryCustLogin(final String userId) throws FSException{	
		StringBuffer sql = new StringBuffer("");
		if(userId.length()<5){
			sql = new StringBuffer("from Customer c where c.custFirstnameEn = :userId ");
		}else{
			sql = new StringBuffer("from Customer c where c.custFirstnameEn like '%'|| :userId ||'%'");
		}
		List list = customerDAO.queryCustLogin(sql.toString(),userId);
		return list;
	}
	
	
	/**
	 * list
	 * @param language
	 * @return
	 * @throws Exception 
	 */
	public List formatUserId(final String userId) throws Exception{		
		StringBuffer sql = new StringBuffer("select trim(rpad(:userId,5,' ')) from dual");
		List list = customerDAO.formatUserId(sql.toString(),userId);
		return list;
	}
	
	
	public void updateApplication(final Application application) throws FSException{
		 StringBuffer hql = new StringBuffer(" update Application set  ");
			hql.append("applyKey = :applyKey,"); 	
			hql.append("finifhDate = :finifhDate,"); 
			hql.append("fee = :fee,");
			hql.append("serviceCost = :serviceCost,"); 	
			hql.append("preDate = :preDate,"); 	
			hql.append("payDate = :payDate,"); 	
			hql.append("hrId = :hrId,"); 	
			hql.append("serviceNumber = :serviceNumber,"); 	
			hql.append("applyType = :applyType,"); 	
			hql.append("status = :status,"); 	
			hql.append("feeStatus = :feeStatus,");
			hql.append("applyArea = :applyArea,");
			hql.append("memo = :memo where applyId = :applyId");
			applicationDAO.updateApplication(hql.toString(),application);
	}	
	
	
	public void deleteApplication(Application application) throws FSException{		
		String hql = " delete from Application c where c.applyId = :applyId";

		applicationDAO.delete(hql,application);					
	}	
	
	
	
	
	

}
